<?php
namespace app\school\controller;
use cmf\controller\AdminBaseController;
use app\school\model\OrderModel;
use app\school\model\SchoolSubjectModel;
use app\school\model\SchoolLevelModel;
use app\school\model\SchoolClassModel;
use app\school\model\SchoolTeacherModel;
use app\school\model\SchoolCourseModel;
use app\school\model\UserStudentModel;
class ExpExcelController  extends AdminBaseController
{
    public function exportExcel($data='', $fileName='', $savePath='./', $isDown=true){  
        
    
        $title = array('ID','订单号','报读课程','班别','课室','家长名称','电话','学员','学校','金额','座位号','支付状态','支付方式','支付时间','退款说明','下单时间','退款时间','备注');
        include(EXTEND_PATH.'phpexcel/PHPExcel.php'); 
        $obj = new \PHPExcel();  
    
        $obj->getActiveSheet()->getColumnDimension('A')->setWidth(6);
        $obj->getActiveSheet()->getColumnDimension('B')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $obj->getActiveSheet()->getColumnDimension('F')->setWidth(10);
        $obj->getActiveSheet()->getColumnDimension('G')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('I')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('N')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('O')->setWidth(25);
        $obj->getActiveSheet()->getColumnDimension('P')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('Q')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('R')->setWidth(18);



        //横向单元格标识  
    
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');  
    
           
    
        $obj->getActiveSheet(0)->setTitle('sheet名称');   //设置sheet名称  
    
        $_row = 1;   //设置纵向单元格标识  
    
        if($title){  
    
            $_cnt = count($title);  
    
         
    
            $i = 0;  
    
            foreach($title AS $v){   //设置列标题  
    
                $obj->setActiveSheetIndex(0)->setCellValue($cellName[$i].$_row, $v);  
    
                $i++;  
    
            }  
    
            $_row++;  
    
        }  
    
        $where=[];    
        $dwhere=[];    
        $swhere=[];
        $param = $this->request->param();     
        $status = $this->request->param('pay_status');       
        $pay_type = $this->request->param('pay_type');       

        if($status != null){
            $dwhere['a.pay_status'] = $status;           
        }
        if($pay_type != null){
            $dwhere['a.pay_type'] = $pay_type;           
        }

        if(!empty($param['course_id'])){
            $dwhere['a.course_id'] = $param['course_id'];
        }
        if(!empty($param['subject_id'])){
            $dwhere['b.subject_id'] = $param['subject_id'];
        }
        if(!empty($param['class_id'])){
            $dwhere['b.class_id'] = $param['class_id'];
        }
        if(!empty($param['teacher_id'])){
            $dwhere['b.teacher_id'] = $param['teacher_id'];
        }
       
        $field = 'a.*,b.id as courseid,b.title,b.subject_id,b.class_id,b.teacher_id ';

        $startTime = empty($param['start_time']) ? 0 : $param['start_time'];
        $endTime   = empty($param['end_time']) ? 0 : $param['end_time'];
        if (!empty($startTime) && !empty($endTime)) {
            $dwhere['a.create_time'] = [['>=', $startTime], ['<=', $endTime]];
        } else {
            if (!empty($startTime)) {
                $dwhere['a.create_time'] = ['>=', $startTime];
            }
            if (!empty($endTime)) {
                $dwhere['a.create_time'] = ['<=', $endTime];
            }
        }

        if(!empty($param['keyword'])){
            $dwhere['a.student_name'] = ['like','%'.$param['keyword'].'%'];
        }
       
        $join = [
            ['school_course b', 'a.course_id = b.id']
         ];
        $model = new OrderModel();
        $data = $model->alias('a')->field($field)
            ->join($join)
            ->where($dwhere)->order("a.id DESC")->select(); 
        
    
        //填写数据  
    
        if($data){  
    
            $i = 0;  
    
            foreach($data AS $_v){  
                $obj->getActiveSheet(0)->setCellValueExplicit($cellName[0] . ($i+$_row), strval($_v['id']),\PHPExcel_Cell_DataType::TYPE_STRING);
                $obj->getActiveSheet(0)->setCellValueExplicit($cellName[1] . ($i+$_row), strval($_v['order_no']),\PHPExcel_Cell_DataType::TYPE_STRING);
                $obj->getActiveSheet(0)->setCellValue($cellName[2] . ($i+$_row), $_v['course']['title']);
                $obj->getActiveSheet(0)->setCellValue($cellName[3] . ($i+$_row), $_v['course']['schoolclass']['name']);
                $obj->getActiveSheet(0)->setCellValue($cellName[4] . ($i+$_row), $_v['course']['classroom']);
                $obj->getActiveSheet(0)->setCellValue($cellName[5] . ($i+$_row), $_v['parent_name']);
                $obj->getActiveSheet(0)->setCellValueExplicit($cellName[6] . ($i+$_row), $_v['phone'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $obj->getActiveSheet(0)->setCellValue($cellName[7] . ($i+$_row), $_v['student_name']);    
                $obj->getActiveSheet(0)->setCellValue($cellName[8] . ($i+$_row), $_v['current_school']);            
                $obj->getActiveSheet(0)->setCellValue($cellName[9] . ($i+$_row), $_v['price']);
                $obj->getActiveSheet(0)->setCellValue($cellName[10] . ($i+$_row), $_v['seat']);
                $obj->getActiveSheet(0)->setCellValue($cellName[11] . ($i+$_row), $_v['pay_status']);
                $obj->getActiveSheet(0)->setCellValue($cellName[12] . ($i+$_row), $_v['pay_type']);
                $obj->getActiveSheet(0)->setCellValue($cellName[13] . ($i+$_row), $_v['pay_time']);               
                $obj->getActiveSheet(0)->setCellValue($cellName[14] . ($i+$_row), $_v['refund_description']);       
                $obj->getActiveSheet(0)->setCellValue($cellName[15] . ($i+$_row), date('Y-m-d H:i',$_v['create_time']));
                $obj->getActiveSheet(0)->setCellValue($cellName[16] . ($i+$_row), $_v['refund_time']==0?'':date('Y-m-d H:i',$_v['refund_time']));      
                $obj->getActiveSheet(0)->setCellValue($cellName[17] . ($i+$_row), $_v['remake']);
                $i++;  
    
            }  
    
        }  
    
           
    
        //文件名处理  
    
        if(!$fileName){  
    
            $fileName = uniqid(time(),true);  
    
        }  
    
       
    
        $objWrite = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');  
    
       
    
        if($isDown){   //网页下载  
    
            header('pragma:public');  
    
            header("Content-Disposition:attachment;filename=$fileName.xls");  
    
            $objWrite->save('php://output');exit;  
    
        }  
    
       
    
        $_fileName = iconv("utf-8", "gb2312", $fileName);   //转码  
    
        $_savePath = $savePath.$_fileName.'.xlsx';  
    
         $objWrite->save($_savePath);  
    
       
    
         return $savePath.$fileName.'.xlsx';  
    
    }  
}